﻿--Lấy danh sách món ăn
CREATE PROCEDURE sp_LayDanhSachMonAn
AS
BEGIN
	SELECT MaMonAn, TenMonAn, CachCheBien FROM dbo.MonAn WHERE TinhTrang = 1
END
GO
--Lay danh sach nguyen lieu theo tung mon an
CREATE PROCEDURE sp_LayDanhSachNguyenLieuTheoMonAn
(
	@MaMonAn int
)
AS
BEGIN
	SELECT nl.MaNguyenLieu, TenNguyenLieu, nl.DonViTinh, ct.SoLuong
	FROM dbo.NguyenLieu nl, dbo.MonAn ma, dbo.ChiTietNguyenLieuMonAn ct
	WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ma.MaMonAn = @MaMonAn AND ct.TinhTrang = 1
END 
GO
--Cap nhat mon an
CREATE PROCEDURE sp_CapNhatMonAn
(
	@MaMonAn int,
	@TenMonAn nvarchar(50),
	@CachCheBien nvarchar(50)
)
AS
BEGIN
	BEGIN TRANSACTION
		UPDATE dbo.MonAn SET TenMonAn = @TenMonAn, CachCheBien = @CachCheBien WHERE MaMonAn = @MaMonAn
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Them mon an
CREATE PROCEDURE sp_ThemMonAn
(
	@TenMonAn nvarchar(50),
	@CachCheBien nvarchar(50)
)
AS
BEGIN
	BEGIN TRANSACTION
		INSERT INTO dbo.MonAn(TenMonAn, CachCheBien, TinhTrang) VALUES (@TenMonAn, @CachCheBien, 1)
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--huy mon an
CREATE PROCEDURE sp_HuyMonAn
(
	@MaMonAn int
)
AS
BEGIN
	BEGIN TRANSACTION
		UPDATE dbo.MonAn SET TinhTrang = 0 WHERE MaMonAn = @MaMonAn
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Lay danh sach nguyen lieu
CREATE PROCEDURE sp_LayDanhSachNguyenLieu
(
	@MaMonAn INT
)
AS
BEGIN
	SELECT MaNguyenLieu, TenNguyenLieu, DonViTinh FROM dbo.NguyenLieu WHERE
		MaNguyenLieu NOT IN (SELECT nl.MaNguyenLieu FROM dbo.NguyenLieu nl, dbo.ChiTietNguyenLieuMonAn ct, dbo.MonAn ma
									WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ma.MaMonAn = @MaMonAn AND ct.TinhTrang = 1)
END
GO
--lay don vi tinh
CREATE PROCEDURE sp_LayDanhSachDonViTinh
AS
BEGIN
	SELECT DonViTinh FROM dbo.NguyenLieu
	GROUP BY DonViTinh
END
GO
--Cap nhat so luong chi tiet nguyen lieu mon an
CREATE PROCEDURE sp_CapNhatSoLuongChiTietNguyenLieuMonAn
(
	@MaMonAn INT,
	@MaNguyenLieu INT,
	@SoLuong INT
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		SET @STT = (SELECT STT FROM dbo.ChiTietNguyenLieuMonAn ct, dbo.MonAn ma, dbo.NguyenLieu nl 
			WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ct.MaNguyenLieu = @MaNguyenLieu AND ct.MaMonAn = @MaMonAn)
		UPDATE dbo.ChiTietNguyenLieuMonAn SET SoLuong = @SoLuong WHERE STT = @STT
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--them nguyen lieu vao mon a
CREATE PROCEDURE sp_ThemNguyenLieuVaoMonAn
(
	@MaMonAn INT,
	@MaNguyenLieu INT,
	@DonViTinh NVARCHAR(Max)
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		SET @STT = (SELECT STT FROM dbo.ChiTietNguyenLieuMonAn ct, dbo.MonAn ma, dbo.NguyenLieu nl 
			WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ct.MaNguyenLieu = @MaNguyenLieu AND ct.MaMonAn = @MaMonAn)
		IF @STT IS NULL
		BEGIN
			INSERT INTO dbo.ChiTietNguyenLieuMonAn
					( MaMonAn ,
					  MaNguyenLieu ,
					  SoLuong ,
					  DonViTinh ,
					  TinhTrang
					)
			VALUES  ( @MaMonAn , -- MaMonAn - int
					  @MaNguyenLieu , -- MaNguyenLieu - int
					  0 , -- SoLuong - int
					  @DonViTinh , -- DonViTinh - nvarchar(50)
					  1  -- TinhTrang - int
					)
		END
		ELSE
		BEGIN
			UPDATE dbo.ChiTietNguyenLieuMonAn SET TinhTrang = 1 WHERE STT = @STT
		END
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Bot nguyen lieu cho mon an
CREATE PROCEDURE sp_BotNguyenLieuChoMonAn
(
	@MaMonAn INT,
	@MaNguyenLieu INT
)
AS
BEGIN
	BEGIN TRANSACTION
		DECLARE @STT INT
		SET @STT = (SELECT STT FROM dbo.ChiTietNguyenLieuMonAn ct, dbo.MonAn ma, dbo.NguyenLieu nl 
			WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ct.MaNguyenLieu = @MaNguyenLieu AND ct.MaMonAn = @MaMonAn)
		UPDATE dbo.ChiTietNguyenLieuMonAn SET TinhTrang = 0 WHERE STT = @STT
		IF(@@ERROR != 0)
		BEGIN
			RAISERROR ('Xảy ra lỗi', 16, 1)
			ROLLBACK
			RETURN
		END
	COMMIT
END
GO
--Lay tong so luong nguyen lieu da dung
CREATE PROCEDURE sp_TongSoLuongNguyenLieuDaDung
(
	@MaNguyenLieu INT
)
AS
BEGIN
	SELECT SUM(CAST(ct.SoLuong AS BIGINT)) FROM dbo.NguyenLieu nl, dbo.ChiTietNguyenLieuMonAn ct, dbo.MonAn ma WHERE ma.MaMonAn = ct.MaMonAn AND ct.MaNguyenLieu = nl.MaNguyenLieu AND ct.MaNguyenLieu = @MaNguyenLieu
END
GO
--Lay so luong nguyen lieu ton kho
CREATE PROCEDURE sp_SoLuongNguyenLieuTonKho
(
	@MaNguyenLieu INT
)
AS
BEGIN
	SELECT SUM(CAST(k.SoLuongTon AS BIGINT)) FROM dbo.NguyenLieu nl, dbo.SanPham sp, dbo.Kho k WHERE nl.MaNguyenLieu = sp.MaNguyenLieu AND sp.MaSP = k.MaSP AND nl.MaNguyenLieu = @MaNguyenLieu
END
GO